' Licence
' * DataGridView Helper - filters and exports the content of a DataGridView base on a Data Table/View
' * Copyright (C) 2008 Mycroft Holme - a CodeProject ID
' *
' * == BEGIN LICENSE ==
' *
' * Licensed under the terms of following license:
' *
' *  - The Code Project Open License 1.02 or later (the "CPOL")
' *    http://www.codeproject.com/info/cpol10.aspx
' *
' * == END LICENSE ==
' 


Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Data
Imports System.Linq
Imports System.Text
Imports System.Windows.Forms
Imports System.IO

Namespace DGVPaste
	Public Partial Class ucDGVHelper
		Public Sub New()
			InitializeComponent()


			pbButton.Size = szSmall
			Me.Size = szSmall

			tsbShow.Tag = "S"
		End Sub

		Private mvDGV As DataGridView
		Public Property oDGV() As DataGridView
			Private Get
				Return mvDGV
			End Get
			Set
				mvDGV = value
				If value IsNot Nothing Then
					Me.Top = value.Top
					Me.Left = value.Left
					Me.Size = szSmall
				End If
			End Set
		End Property

		#Region "private vars"
		Private szLrg As New Size(25, 25)
		Private szSmall As New Size(10, 10)
		Private lPanels As New List(Of Panel)()
		Private lFilterDefs As New List(Of FilterDef)()
		Private Property IsLoaded() As Boolean
			Get
				Return m_IsLoaded
			End Get
			Set
				m_IsLoaded = Value
			End Set
		End Property
		Private m_IsLoaded As Boolean
		'filter has been loaded
		Private Property ButtonClicked() As Boolean
			Get
				Return m_ButtonClicked
			End Get
			Set
				m_ButtonClicked = Value
			End Set
		End Property
		Private m_ButtonClicked As Boolean
		'user clicked the button to expand the filter
		Private Property EventAdded() As Boolean
			Get
				Return m_EventAdded
			End Get
			Set
				m_EventAdded = Value
			End Set
		End Property
		Private m_EventAdded As Boolean
		Private Const MinDate As String = "01/01/1900"
		#End Region

		Private Sub ShowFilter()
			Try
				'set the color of the button based on if there is a filter set
				Dim cLoaded As Color = Color.Bisque
				If IsLoaded Then
					cLoaded = Color.Tomato
				End If
				Me.pbButton.BackColor = cLoaded

				'use the size to determine if the filter is currently displayed
				If Me.Size <> szSmall Then
					'hide the filter 
					pbButton.Size = szSmall
					Me.Size = szSmall
					Return
				End If

				'check if the data source has benn processed before
				ParseDataSource()

				'set the panel size
				Me.Width = 240
				SetPanelHeight()
				pbButton.Size = szLrg
			Catch generatedExceptionName As Exception
				Throw
			End Try
		End Sub

		''' <summary>
		''' calc the panel height based on the number of field controls displayed
		''' </summary>
		Private Sub SetPanelHeight()
			Dim iHeight As Integer = Me.toolStrip1.Height + (pnlData.Controls.Count * 22)
			Me.Height = iHeight
			Me.Top = oDGV.Top
			Me.Left = oDGV.Left
			Me.BringToFront()
		End Sub

		''' <summary>
		''' check the existence and that the type of datasource is valid (BindingSource or later DAtatable/view)
		''' </summary>
		''' <returns></returns>
		Private Function ParseDataSource() As Boolean
			If oDGV Is Nothing Then
				Return False
			End If

			If oDGV.DataSource Is Nothing Then
				Return False
			End If

			If Me.pnlData.Controls.Count > 0 Then
				Return True
			End If

			'get the bindingsource from the DGV
			If oDGV.DataSource.[GetType]().Equals(GetType(System.Windows.Forms.BindingSource)) Then
				AssignEvent()
				DoBS(DirectCast(oDGV.DataSource, BindingSource))
			End If

			Return True
		End Function

		''' <summary>
		''' Extract the filter information from the bindingsource object
		''' </summary>
		''' <param name="oBS"></param>
		Private Sub DoBS(oBS As BindingSource)
			Dim oFD As FilterDef
			Dim oTable As DataTable = Nothing

			'test for a dataview
			If oBS.DataSource.[GetType]().Equals(GetType(System.Data.DataView)) Then
				oTable = DirectCast(oBS.DataSource, DataView).Table
			End If

			If oBS.DataSource.[GetType]().Equals(GetType(System.Data.DataTable)) Then
				oTable = DirectCast(oBS.DataSource, DataTable)
			End If

			If oTable IsNot Nothing Then
				lFilterDefs.Clear()
				lPanels.Clear()
				For Each oCol As DataColumn In oTable.Columns
					oFD = New FilterDef()
					oFD.ColumnName = oCol.ColumnName
					oFD.Caption = oCol.Caption
					oFD.Ordinal = oCol.Ordinal

					'check there is a column in the DGV and if is is visible
					For Each C As DataGridViewColumn In oDGV.Columns
						If C.Name = oCol.ColumnName Then
							oFD.IsVisible = C.Visible
							Exit For
						End If
					Next

					Select Case oCol.DataType.Name.ToString().ToLower()
						Case "boolean"
							oFD.DataType = FilterDef.enDataType.[Boolean]
							Exit Select
						Case "int", "int32", "int64", "numeric", "decimal"
							oFD.DataType = FilterDef.enDataType.Numeric
							Exit Select
						Case "datetime", "date"
							oFD.DataType = FilterDef.enDataType.[Date]
							Exit Select
						Case Else

							oFD.DataType = FilterDef.enDataType.[String]
							Exit Select
					End Select
					lFilterDefs.Add(oFD)
					Dim oPanel As Panel = Nothing
					oPanel = AddSet(oFD)


					If oPanel IsNot Nothing Then

						lPanels.Add(oPanel)
					End If
				Next

				For i As Integer = lPanels.Count To 1 Step -1
					oFD = DirectCast(lPanels(i - 1).Tag, FilterDef)
					If oFD.IsVisible Then
						Me.pnlData.Controls.Add(lPanels(i - 1))
					End If
				Next
			End If
			'
		End Sub


		''' <summary>
		''' Add a set of controls to the panel for the filter definition
		''' </summary>
		''' <param name="oDef">filter definition class</param>
		''' <returns>a populated panel</returns>
		Private Function AddSet(oDef As FilterDef) As Panel
			Dim oPanel As New Panel()
			Dim oCBO As New ComboBox()
			Dim oLabel As New Label()
			Dim ctrlFilter As Control
			'set the panel details
			oPanel.Height = 21
			oPanel.Tag = oDef
			oPanel.Dock = DockStyle.Top

			'setup the label 
			oLabel.Width = 120
			oLabel.Text = oDef.Caption
			oLabel.TextAlign = ContentAlignment.MiddleRight
			oLabel.Dock = DockStyle.Left

			'filter the datatype table for display in the combo
			Dim sFilter As String = String.Format("DataType = '{0}'", oDef.DataType.ToString())
			Dim oDV As New DataView(oDef.dtOp.Copy(), sFilter, "", DataViewRowState.CurrentRows)
			oCBO.Width = 20
			oCBO.Dock = DockStyle.Fill
			oCBO.DropDownStyle = ComboBoxStyle.DropDownList
			oCBO.DisplayMember = "Operator"
			oCBO.ValueMember = "Operator"
			oCBO.DataSource = oDV
			oCBO.Name = String.Format("cbo{0}", oDef.Ordinal)

			Select Case oDef.DataType
				Case FilterDef.enDataType.[Boolean]
					Dim oChk As New CheckBox()
					oChk.Name = String.Format("ctrlF{0}", oDef.Ordinal)
					oChk.Width = 80
					oChk.Dock = DockStyle.Left
					oChk.ThreeState = True
					ctrlFilter = oChk

					'combo is bot required
					oCBO.Visible = False
					Exit Select
				Case FilterDef.enDataType.[Date]
					Dim oDte As New DateTimePicker()
					oDte.Name = String.Format("ctrlF{0}", oDef.Ordinal)
					oDte.Format = DateTimePickerFormat.[Short]
					oDte.Width = 80
					oDte.Dock = DockStyle.Left
					oDte.Value = Convert.ToDateTime(MinDate)
					ctrlFilter = oDte
					Exit Select
				Case Else
					'set the textbox
					Dim oTB As New TextBox()
					oTB.Width = 80
					oTB.Dock = DockStyle.Left
					oTB.Name = String.Format("ctrlF{0}", oDef.Ordinal)
					ctrlFilter = oTB
					Exit Select
			End Select

			'add the controls to the panel
			oPanel.Controls.Add(oCBO)
			oPanel.Controls.Add(ctrlFilter)
			oPanel.Controls.Add(oLabel)

			Return oPanel

		End Function


		''' <summary>
		''' Hide/Dispay any columns that are not visible in the datagridview
		''' </summary>
		''' <param name="sender"></param>
		''' <param name="e"></param>
		Private Sub tsbShow_Click(sender As Object, e As EventArgs)
			Dim sLabel As String = "S"
			Dim bHide As Boolean = TryCast(sender, ToolStripButton).Tag.ToString() = "H"
			Dim oFD As FilterDef

			pnlData.Controls.Clear()
			For i As Integer = lPanels.Count To 1 Step -1
				oFD = DirectCast(lPanels(i - 1).Tag, FilterDef)
				If oFD.IsVisible Then
					Me.pnlData.Controls.Add(lPanels(i - 1))
				Else
					If Not bHide Then
						Me.pnlData.Controls.Add(lPanels(i - 1))
					End If
				End If
			Next
			SetPanelHeight()
			If Not bHide Then
				sLabel = "H"
			End If

			TryCast(sender, ToolStripButton).Tag = sLabel
		End Sub

		Private Sub tsbApply_Click(sender As Object, e As EventArgs)
			Dim sFilter As New StringBuilder()
			Dim sCtrl As String
			Dim oCB As ComboBox
			For Each oPanel As Panel In pnlData.Controls
				Dim oDef As FilterDef = DirectCast(oPanel.Tag, FilterDef)

				'get the controls manually as the binding does not seem to work
				sCtrl = String.Format("ctrlF{0}", oDef.Ordinal)
				oDef.FilterValue = FilterGetSet(oPanel.Controls.Find(sCtrl, False)(0), False)

				sCtrl = String.Format("cbo{0}", oDef.Ordinal)
				oCB = DirectCast(oPanel.Controls.Find(sCtrl, False)(0), ComboBox)
				oDef.[Operator] = oCB.Text

				If Not String.IsNullOrEmpty(oDef.FilterValue) AndAlso Not String.IsNullOrEmpty(oDef.[Operator]) Then
					Select Case oDef.DataType
						Case FilterDef.enDataType.[Boolean]
							If sFilter.Length = 0 Then
								sFilter.AppendFormat("{0} = {1}", oDef.ColumnName, oDef.FilterValue)
							Else
								sFilter.AppendFormat(" and {0} = {1}", oDef.ColumnName, oDef.FilterValue)
							End If

							Exit Select
						Case FilterDef.enDataType.Numeric
							If sFilter.Length = 0 Then
								sFilter.AppendFormat("{0} {1} {2}", oDef.ColumnName, oDef.[Operator], oDef.FilterValue)
							Else
								sFilter.AppendFormat(" and {0} {1} {2}", oDef.ColumnName, oDef.[Operator], oDef.FilterValue)
							End If

							Exit Select
						Case FilterDef.enDataType.[Date]
							If sFilter.Length = 0 Then
								sFilter.AppendFormat("{0} {1} '{2}'", oDef.ColumnName, oDef.[Operator], oDef.FilterValue)
							Else
								sFilter.AppendFormat(" and {0} {1} '{2}'", oDef.ColumnName, oDef.[Operator], oDef.FilterValue)
							End If

							Exit Select
						Case FilterDef.enDataType.[String]
							If sFilter.Length = 0 Then
								sFilter.AppendFormat("{0} {1} '{2}%'", oDef.ColumnName, oDef.[Operator], oDef.FilterValue)
							Else
								sFilter.AppendFormat(" and {0} {1} '{2}%'", oDef.ColumnName, oDef.[Operator], oDef.FilterValue)
							End If
							Exit Select
					End Select
				End If
			Next
			If sFilter.Length > 0 Then
				Dim oBS As BindingSource = DirectCast(oDGV.DataSource, BindingSource)
				oBS.Filter = sFilter.ToString()
				Me.FindForm().Text = sFilter.ToString()
			End If
			IsLoaded = sFilter.Length > 0
			ShowFilter()
		End Sub

		Private Sub tsbReset_Click(sender As Object, e As EventArgs)
			Dim sCtrl As String
			For Each oDef As FilterDef In lFilterDefs
				oDef.FilterValue = String.Empty
				oDef.[Operator] = String.Empty
			Next
			Dim oBS As BindingSource = DirectCast(oDGV.DataSource, BindingSource)
			oBS.Filter = String.Empty
			For Each oPanel As Panel In pnlData.Controls
				Dim oDef As FilterDef = DirectCast(oPanel.Tag, FilterDef)

				'get the controls manually as the binding does not seem to work
				'Set the value back to the default
				sCtrl = String.Format("ctrlF{0}", oDef.Ordinal)
				Dim oCtrl As Control = DirectCast(oPanel.Controls.Find(sCtrl, False)(0), Control)
				FilterGetSet(oCtrl, True)

				sCtrl = String.Format("cbo{0}", oDef.Ordinal)
				If oPanel.Controls.Find(sCtrl, False).Count() > 0 Then
					Dim oCB As ComboBox = DirectCast(oPanel.Controls.Find(sCtrl, False)(0), ComboBox)
					oCB.SelectedIndex = 0
				End If
			Next
			IsLoaded = False
			ShowFilter()
		End Sub

		Private Function FilterGetSet(ctrlFilter As Control, bReset As Boolean) As String
			Dim sValue As String = String.Empty

			'reset the filter control
			If ctrlFilter.[GetType]().Equals(GetType(System.Windows.Forms.TextBox)) Then
				If Not bReset Then
					sValue = TryCast(ctrlFilter, TextBox).Text
				Else
					TryCast(ctrlFilter, TextBox).Text = String.Empty
				End If
			End If
			If ctrlFilter.[GetType]().Equals(GetType(System.Windows.Forms.DateTimePicker)) Then
				If Not bReset Then
					If TryCast(ctrlFilter, DateTimePicker).Value = Convert.ToDateTime(MinDate) Then
						sValue = String.Empty
					Else
						sValue = TryCast(ctrlFilter, DateTimePicker).Value.ToString("dd/MMM/yyyy")
					End If
				Else
					TryCast(ctrlFilter, DateTimePicker).Value = Convert.ToDateTime(MinDate)
				End If
			End If
			If ctrlFilter.[GetType]().Equals(GetType(System.Windows.Forms.CheckBox)) Then
				If Not bReset Then
					sValue = TryCast(ctrlFilter, CheckBox).Checked.ToString()
				Else
					TryCast(ctrlFilter, CheckBox).CheckState = CheckState.Indeterminate
				End If
			End If

			Return sValue

		End Function

        Private Sub pictureBox1_Click(sender As Object, e As EventArgs)
            ButtonClicked = Not ButtonClicked
            ShowFilter()
        End Sub

		Private Sub AssignEvent()
			If EventAdded Then
				Return
			End If
			AddHandler DirectCast(oDGV.DataSource, BindingSource).DataSourceChanged, New EventHandler(AddressOf BindingSourceDataSourceChanged)
			EventAdded = True
		End Sub

		Private Sub BindingSourceDataSourceChanged(sender As Object, e As EventArgs)
			Try
				TryCast(sender, BindingSource).Filter = String.Empty
				pnlData.Controls.Clear()
				ButtonClicked = False
				pnlData.Controls.Clear()
				lFilterDefs.Clear()
				pbButton.Size = szSmall
				Me.Size = szSmall
			Catch generatedExceptionName As Exception
				Throw
			End Try
		End Sub

		#Region "FilterDef - Helper class to manage the filter definition controls"
		''' <summary>
		''' Helper class to manage the filter definition controls
		''' </summary>
		Private Class FilterDef
			Public Sub New()
				dtOp = New DataTable()
				dtOp.Columns.Add("Operator", GetType(System.String))
				dtOp.Columns.Add("DataType", GetType(System.String))

				dtOp.Rows.Add("like", "String")

				dtOp.Rows.Add("=", "String")
				dtOp.Rows.Add("=", "Date")
				dtOp.Rows.Add("=", "Boolean")
				dtOp.Rows.Add("=", "Numeric")


				dtOp.Rows.Add("<", "Numeric")
				dtOp.Rows.Add("<", "Date")

				dtOp.Rows.Add(">", "Numeric")
				dtOp.Rows.Add(">", "Date")
			End Sub
			Public Enum enDataType
				[String]
				Numeric
				[Date]
				[Boolean]
			End Enum


			Public Property ColumnName() As String
				Get
					Return m_ColumnName
				End Get
				Set
					m_ColumnName = Value
				End Set
			End Property
			Private m_ColumnName As String
			Public Property Caption() As String
				Get
					Return m_Caption
				End Get
				Set
					m_Caption = Value
				End Set
			End Property
			Private m_Caption As String
			Public Property DataType() As enDataType
				Get
					Return m_DataType
				End Get
				Set
					m_DataType = Value
				End Set
			End Property
			Private m_DataType As enDataType
			Public Property FilterValue() As String
				Get
					Return m_FilterValue
				End Get
				Set
					m_FilterValue = Value
				End Set
			End Property
			Private m_FilterValue As String
			Public Property IsVisible() As Boolean
				Get
					Return m_IsVisible
				End Get
				Set
					m_IsVisible = Value
				End Set
			End Property
			Private m_IsVisible As Boolean
			Public Property [Operator]() As String
				Get
					Return m_Operator
				End Get
				Set
					m_Operator = Value
				End Set
			End Property
			Private m_Operator As String
			Public Property Ordinal() As Integer
				Get
					Return m_Ordinal
				End Get
				Set
					m_Ordinal = Value
				End Set
			End Property
			Private m_Ordinal As Integer

			Public dtOp As DataTable


		End Class
		#End Region
	End Class
End Namespace
